package dao;

import db.DbUtil;
import entity.Goods;
import entity.User;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

public class GoodsDao {

	public List<Goods> selectAllGoods() {
		List<Goods> list= DbUtil.getInstance().queryList("select * from goods",null,Goods.class);
		return list;
	}

	public List<Goods> selectGoodsByType(Integer typeId) {
		List<Goods> list= DbUtil.getInstance().queryList("select * from goods where type_id=?",new Object[] {typeId},Goods.class);
		return list;
	}

	public List<Goods> selectHotGoods() {
		List<Goods> list= DbUtil.getInstance().queryList("select goods_id,goods_name,type_id,goods_image,goods_desc,goods_price,goods_discount,supplier,sales_qty,stock_qty,create_time,update_time from hottest_goods order by heat_score desc limit 12",null,Goods.class);
		return list;
	}

	public List<Goods> selectSpecialGoods() {
		List<Goods> list= DbUtil.getInstance().queryList("select goods_id,goods_name,type_id,goods_image,goods_desc,goods_price,goods_discount,supplier,sales_qty,stock_qty,create_time,update_time from special_offers order by goods_discount limit 12",null,Goods.class);
		return list;
	}

	public List<Goods> selectNewGoods() {
		List<Goods> list= DbUtil.getInstance().queryList("select goods_id,goods_name,type_id,goods_image,goods_desc,goods_price,goods_discount,supplier,sales_qty,stock_qty,create_time,update_time from goods order by create_time desc limit 12",null,Goods.class);
		return list;
	}




	public Integer total_goods(String condition, List<Object> parmas) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			String sql = "select count(*) from goods where "+ condition;
			ps = conn.prepareStatement(sql);
			for (int i =1;i <= parmas.size();i++){
				ps.setObject(i,parmas.get(i-1));
			}
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(ps, conn);
		}
		return 0;
	}

	public List<Goods> queryGoodsPage(int start, int size, String condition, List<Object> parmas) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			String sql = "select * from goods where "+ condition +" limit ?,?";
			System.out.println(sql);
			ps = conn.prepareStatement(sql);
			for (int i =1;i <= parmas.size();i++){
				ps.setObject(i,parmas.get(i-1));
			}
			ps.setObject(parmas.size()+1,start);
			ps.setObject(parmas.size()+2,size);

			ResultSet rs = ps.executeQuery();
			List<Goods> list = new ArrayList<>();
			while(rs.next()) {
				Goods good = new Goods();
				good.setGoodsId(rs.getInt("goods_id"));
				good.setGoodsName(rs.getString("goods_name"));
				good.setTypeId(rs.getInt("type_id"));
				good.setGoodsImage(rs.getString("goods_image"));
				good.setGoodsDesc(rs.getString("goods_desc"));
				good.setGoodsPrice(rs.getBigDecimal("goods_price"));
				good.setGoodsDiscount(rs.getBigDecimal("goods_discount"));
				good.setSupplier(rs.getString("supplier"));
				good.setSalesQty(rs.getInt("sales_qty"));
				good.setStockQty(rs.getInt("stock_qty"));
				if (rs.getTimestamp("create_time") != null) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					good.setCreateTime(sdf.format(rs.getTimestamp("create_time")));
				}
				if (rs.getTimestamp("update_time") != null) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					good.setUpdateTime(sdf.format(rs.getTimestamp("update_time")));
				}
				list.add(good);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(ps, conn);
		}
		return null;
	}

	public int deleteGood(Integer id) {
		int ret = DbUtil.getInstance().execute("delete from goods where goods_id=?", new Object[]{id});
		return ret;
	}

	public Goods selectGoodByID(Integer id) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			String sql = "select * from goods where goods_id=" + id;
			ResultSet rs = stmt.executeQuery(sql);
			Goods good = null;
			if(rs.next()) {
				good = new Goods();
				good.setGoodsId(rs.getInt("goods_id"));
				good.setGoodsName(rs.getString("goods_name"));
				good.setTypeId(rs.getInt("type_id"));
				good.setGoodsImage(rs.getString("goods_image"));
				good.setGoodsDesc(rs.getString("goods_desc"));
				good.setGoodsPrice(rs.getBigDecimal("goods_price"));
				good.setGoodsDiscount(rs.getBigDecimal("goods_discount"));
				good.setSupplier(rs.getString("supplier"));
				good.setSalesQty(rs.getInt("sales_qty"));
				good.setStockQty(rs.getInt("stock_qty"));
				if (rs.getTimestamp("create_time") != null) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					good.setCreateTime(sdf.format(rs.getTimestamp("create_time")));
				}
				if (rs.getTimestamp("update_time") != null) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					good.setUpdateTime(sdf.format(rs.getTimestamp("update_time")));
				}
			}
			return good;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(stmt, conn);
		}
		return null;
	}

	private Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/shopping?useUnicode=true&characterEncoding=utf8",
					"root", "123456");
			return conn;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	private void closeConnection(Statement stmt, Connection conn) {
		try {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public int addGoods(String addGoodName, String typeId, String goodsImage, String addDesc, String addPrice, String addDiscount, String addSupplier, String addSalesQty, String addStockQty) {
		LocalDateTime currentTime = LocalDateTime.now();
		DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
		String createTime = currentTime.format(formatter);
		int ret = DbUtil.getInstance().execute("insert into goods(goods_name,type_id,goods_image,goods_desc,goods_price,goods_discount,supplier,sales_qty,stock_qty,create_time,update_time) values(?,?,?,?,?,?,?,?,?,?,?)", new Object[]{addGoodName,typeId,goodsImage,addDesc,addPrice,addDiscount,addSupplier,addSalesQty,addStockQty,createTime,createTime});
		return ret;
	}

	public int sureGoods(String addGoodName, String typeId, String addSupplier) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			String sql = "select count(*) from goods where goods_name=" + addGoodName + " and type_id=" + typeId + " and supplier=" + addSupplier;
			ResultSet rs = stmt.executeQuery(sql);
			if(rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(stmt, conn);
		}
		return 0;
	}

}
